# @Project: diablo
# @File: quality_init_timing_zy
# @Time: 2024/3/15 16:26
# @User: Jolly
# @Email: jolly@007vin.com
"""
宏德总仓一线品质质保互相覆盖
"""

import datetime
import time
import threading
import requests
from queue import Queue
from copy import deepcopy
from pypinyin import lazy_pinyin, Style

from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
import pymysql

from dbutils.pooled_db import PooledDB

lock = threading.Lock()


class QualityInit:

    def __init__(self):

        self.init_quality = {}

        self.init_zhibao = {}

        start_time = time.time()

        self.main_group = ["3", "1390f84351fe464b90dcdbf3a164ff24"]

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=10,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 50  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def get_stock_related(self, merchant_id):
        """
        查找库存关联表查找有效的库存关系
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                                     erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1 and
                                      merchant_id='{merchant_id}' order by id asc"""
            # print(f"sql-----{sql}")
            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:

                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_cst_goods(self, cst_merchant_id):
        """
        根据厂商通id查找所有商品数据
        """

        # 测试环境
        # url = "http://192.168.191.131:8900/v1/api/openapi/goods/xqp/alllist"
        # 正式环境
        url = "http://192.168.191.245:9960/v1/api/openapi/goods/xqp/alllist"

        headers = {
            "Content-Type": "application/json"
        }

        params = {
            'yc_id': 'xqp0000000000000000001',
            'merchant_id': cst_merchant_id
        }
        response = requests.get(url=url, headers=headers, params=params)
        resp = response.json()

        if resp.get('code') != 1:
            print("厂商通id查找所有商品数据失败")

        return resp['data']

    def check_quality_exist(self, merchant_id, ori_quality):
        """
        确认品质是否存在
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select ori_quality, quality from diablo_quality_replace where merchant_id='{merchant_id}'
             and ori_quality='{ori_quality}' and status=1"""

            cursor.execute(sql)

            result = cursor.fetchall()
            if len(result) > 0:

                return result[0][0], result[0][1], True

        except Exception as e:
            print("获取品质信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return "", "", False

    def get_quality_zhibao(self, quality, merchant_id):
        """
        获取当前公司质保记录
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select quality_name, quality_pinyin, quality_pinyin_initials, warranty, agreement_type,
             stocks_amount, stocks_total from diablo_quality_tags where merchant_id='{merchant_id}'
              and quality_name='{quality}' and status=1 and agreement_type='stocks'"""

            cursor.execute(sql)

            result = cursor.fetchall()
            if len(result) > 0:

                return result[0][0], result[0][3], True

        except Exception as e:
            print("获取品质质保信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return "", "", False

    def init_hongde_quality_data(self, merchant_id):
        """
        初始化品质数据，宏德和一线
        """
        mysql_conn = self.mysql_conn.connection()

        merchant_quality_data = {}

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select merchant_id, ori_quality, quality from diablo_quality_replace where merchant_id='{merchant_id}' and status=1"""

            cursor.execute(sql)

            result = cursor.fetchall()
            if len(result) > 0:

                for data in result:
                    merchant_id = data[0]
                    ori_quality = data[1]
                    quality = data[2]

                    quality_data = merchant_quality_data.get(merchant_id)
                    if quality_data:
                        temp_data = deepcopy(quality_data)
                        temp_data[ori_quality] = quality
                        merchant_quality_data[merchant_id] = temp_data
                    else:
                        merchant_quality_data[merchant_id] = {ori_quality: quality}

            return merchant_quality_data, False

        except Exception as e:
            print("获取品质信息失败 - mysql：", e)
            return merchant_quality_data, True

        finally:
            mysql_conn.close()

    def init_hongde_zhibao_data(self, merchant_id):
        """
        初始化质保数据，宏德和一线
        """
        mysql_conn = self.mysql_conn.connection()

        merchant_zhibao_data = {}

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select merchant_id, quality_name, quality_pinyin, quality_pinyin_initials, warranty, agreement_type,
                         stocks_amount, stocks_total from diablo_quality_tags where merchant_id='{merchant_id}' and
                          status=1 and agreement_type='stocks'"""

            cursor.execute(sql)

            result = cursor.fetchall()
            for data in result:
                merchant_id = data[0]

                zhibao = {
                    "user_id": 0,
                    "quality_name": data[1],
                    "quality_pinyin": data[2],
                    "quality_pinyin_initials": data[3],
                    "warranty": data[4],
                    "status": 1,
                    "agreement_type": data[5],
                    "stocks_amount": data[6],
                    "stocks_total": data[7],
                }

                quality_data = merchant_zhibao_data.get(merchant_id)
                if quality_data:
                    temp_data = deepcopy(quality_data)
                    temp_data[data[1]] = zhibao
                    merchant_zhibao_data[merchant_id] = temp_data
                else:
                    merchant_zhibao_data[merchant_id] = {data[1]: zhibao}

            return merchant_zhibao_data, False

        except Exception as e:
            print("获取质保信息失败 - mysql：", e)
            return merchant_zhibao_data, True

        finally:
            mysql_conn.close()

    def delete_quality_data(self, merchant_id, ori_quality):
        """
        删除指定商家品质质保数据
        """
        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"DELETE from diablo_quality_replace where merchant_id ='{merchant_id}' and ori_quality='{ori_quality}' and status=1"

            cursor.execute(sql)
            # 提交事务
            mysql_conn.commit()

            return True, False

        except Exception as e:
            print("删除品质数据失败 - mysql：", e)
            return False, True

        finally:
            mysql_conn.close()

    def delete_zhibao_data(self, merchant_id, quality):
        """
        删除指定商家品质质保数据
        """
        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"DELETE from diablo_quality_tags where merchant_id ='{merchant_id}' and quality_name='{quality}' and" \
                  f" status=1 and agreement_type='stocks'"

            cursor.execute(sql)
            # 提交事务
            mysql_conn.commit()

            return True, False

        except Exception as e:
            print("删除质保数据失败 - mysql：", e)
            return False, True

        finally:
            mysql_conn.close()

    # 写入数据
    def write_data(self, *args):

        goods = args[0]
        stock_related = args[1]

        ori_quality = goods.get("brand_names")

        with lock:

            ori_quality_, quality, check = self.check_quality_exist(stock_related.get("merchant_id"), ori_quality)
            if check:

                hongde_quality = ""
                # 如果当前商户品质记录存在，则需要查看，该商户这个品质映射是否和宏德或者一线的一致，不一致需要删除，再写入新的
                for _, init_data in self.init_quality.items():
                    quality_ = init_data.get(ori_quality_)
                    if quality_:
                        hongde_quality = quality_
                        break
                    else:
                        continue

                if hongde_quality != "":
                    # 过滤了分公司独有的品质，这里说明宏德也有这个品质数据，这里再比较是否一致
                    if quality != hongde_quality:
                        # 不一致，需要删除，再写入宏德的

                        # 删除旧的， 包括质保
                        self.delete_quality_data(stock_related.get("merchant_id"), ori_quality_)
                        # self.delete_zhibao_data(stock_related.get("merchant_id"), hongde_quality)

                        # 写入新的， 组装插入数据
                        mysql_conn = self.mysql_conn.connection()

                        try:
                            cursor = mysql_conn.cursor()

                            quality_pinyin = ''.join(lazy_pinyin(quality))
                            quality_pinyin_initials = ''.join(lazy_pinyin(quality, style=Style.FIRST_LETTER))

                            key = ['status', 'merchant_id', 'app_key', 'ori_quality', 'quality',
                                   'quality_py', 'create_time', 'update_time']

                            formatted_datetime = datetime.datetime.now().strftime('%Y-%m-%d') + " 00:00:00"

                            key_param = [1, stock_related.get("merchant_id"), stock_related.get("app_key"),
                                         ori_quality_, hongde_quality, quality_pinyin_initials, formatted_datetime, formatted_datetime]

                            value = ""

                            for i, v in enumerate(key):
                                value += '"{}",'.format(key_param[i])

                            values = ", ".join('{0}=values({0})'.format(k) for k in key)

                            sql = '''insert into diablo_quality_replace ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                                ",".join(key), value[:-1], values)
                            # print(sql)
                            cursor.execute(sql)
                            mysql_conn.commit()

                        except Exception as e:
                            print("写入品质失败 - mysql：", e)
                        finally:
                            mysql_conn.close()

                    # 说明该分公司和宏德的这个品质保持一致，这时候再比对质保是否一致，不一致需要删除重新写入

                    quality_name, warranty, check = self.get_quality_zhibao(quality, stock_related.get("merchant_id"))
                    if check:

                        # 处理质保信息，写入新的
                        zhibao = {}
                        for _, init_zhibao_data in self.init_zhibao.items():
                            zhibao = init_zhibao_data.get(quality)
                            if zhibao:
                                break

                        if zhibao:
                            # 确保总仓或者一线有同样的质保

                            if zhibao.get('warranty') != warranty:

                                # 删除旧的质保
                                self.delete_zhibao_data(stock_related.get("merchant_id"), quality)

                                mysql_conn = self.mysql_conn.connection()

                                try:
                                    cursor = mysql_conn.cursor()

                                    key = ['user_id', 'merchant_id', 'quality_name', 'quality_pinyin',
                                           'quality_pinyin_initials',
                                           'warranty', 'status', 'agreement_type', 'stocks_amount', 'stocks_total']

                                    key_param = [zhibao.get('user_id'),
                                                 stock_related.get("merchant_id"),
                                                 zhibao.get('quality_name'),
                                                 zhibao.get('quality_pinyin'),
                                                 zhibao.get('quality_pinyin_initials'),
                                                 zhibao.get('warranty'),
                                                 zhibao.get('status'),
                                                 zhibao.get('agreement_type'),
                                                 zhibao.get('stocks_amount'),
                                                 zhibao.get('stocks_total')]

                                    value = ""

                                    for i, v in enumerate(key):
                                        value += '"{}",'.format(key_param[i])

                                    values = ", ".join('{0}=values({0})'.format(k) for k in key)

                                    sql = '''insert into diablo_quality_tags ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                                        ",".join(key), value[:-1], values)
                                    # print(sql)
                                    cursor.execute(sql)
                                    mysql_conn.commit()

                                except Exception as e:
                                    print("写入品质质保信息失败 - mysql：", e)
                                finally:
                                    mysql_conn.close()

                                return
                    else:
                        zhibao = {}
                        for _, init_zhibao_data in self.init_zhibao.items():
                            zhibao = init_zhibao_data.get(quality)
                            if zhibao:
                                break

                        if zhibao:

                            mysql_conn = self.mysql_conn.connection()

                            try:
                                cursor = mysql_conn.cursor()

                                key = ['user_id', 'merchant_id', 'quality_name', 'quality_pinyin',
                                       'quality_pinyin_initials',
                                       'warranty', 'status', 'agreement_type', 'stocks_amount', 'stocks_total']

                                key_param = [zhibao.get('user_id'),
                                             stock_related.get("merchant_id"),
                                             zhibao.get('quality_name'),
                                             zhibao.get('quality_pinyin'),
                                             zhibao.get('quality_pinyin_initials'),
                                             zhibao.get('warranty'),
                                             zhibao.get('status'),
                                             zhibao.get('agreement_type'),
                                             zhibao.get('stocks_amount'),
                                             zhibao.get('stocks_total')]

                                value = ""

                                for i, v in enumerate(key):
                                    value += '"{}",'.format(key_param[i])

                                values = ", ".join('{0}=values({0})'.format(k) for k in key)

                                sql = '''insert into diablo_quality_tags ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                                    ",".join(key), value[:-1], values)
                                # print(sql)
                                cursor.execute(sql)
                                mysql_conn.commit()

                            except Exception as e:
                                print("写入品质质保信息失败 - mysql：", e)
                            finally:
                                mysql_conn.close()

                            return
            else:

                # 如果不存在，就直接写入
                quality = ""
                # 查找宏德品质映射关系，拼接写入
                for _, init_data in self.init_quality.items():
                    quality_ = init_data.get(ori_quality)
                    if quality_:
                        quality = quality_
                        break
                    else:
                        continue

                if quality == "":
                    quality = ori_quality

                # 组装插入数据
                mysql_conn = self.mysql_conn.connection()

                try:
                    cursor = mysql_conn.cursor()

                    quality_pinyin = ''.join(lazy_pinyin(quality))
                    quality_pinyin_initials = ''.join(lazy_pinyin(quality, style=Style.FIRST_LETTER))

                    key = ['status', 'merchant_id', 'app_key', 'ori_quality', 'quality',
                           'quality_py', 'create_time', 'update_time']

                    formatted_datetime = datetime.datetime.now().strftime('%Y-%m-%d') + " 00:00:00"

                    key_param = [1, stock_related.get("merchant_id"), stock_related.get("app_key"),
                                 ori_quality, quality, quality_pinyin_initials, formatted_datetime, formatted_datetime]

                    value = ""

                    for i, v in enumerate(key):
                        value += '"{}",'.format(key_param[i])

                    values = ", ".join('{0}=values({0})'.format(k) for k in key)

                    sql = '''insert into diablo_quality_replace ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                        ",".join(key), value[:-1], values)
                    # print(sql)
                    cursor.execute(sql)
                    mysql_conn.commit()

                except Exception as e:
                    print("写入品质失败 - mysql：", e)
                finally:
                    mysql_conn.close()

                warranty_data = {}

                # 写入质保数据 --------------------------------------------------------
                # 查找宏德对应质保
                for _, init_zhiabo_data in self.init_zhibao.items():
                    warranty_data = init_zhiabo_data.get(quality)
                    if warranty_data:
                        break

                if warranty_data:

                    mysql_conn = self.mysql_conn.connection()

                    try:
                        cursor = mysql_conn.cursor()

                        key = ['user_id', 'merchant_id', 'quality_name', 'quality_pinyin',
                               'quality_pinyin_initials',
                               'warranty', 'status', 'agreement_type', 'stocks_amount', 'stocks_total']

                        key_param = [warranty_data.get('user_id'),
                                     stock_related.get("merchant_id"),
                                     warranty_data.get('quality_name'),
                                     warranty_data.get('quality_pinyin'),
                                     warranty_data.get('quality_pinyin_initials'),
                                     warranty_data.get('warranty'),
                                     warranty_data.get('status'),
                                     warranty_data.get('agreement_type'),
                                     warranty_data.get('stocks_amount'),
                                     warranty_data.get('stocks_total')]

                        value = ""

                        for i, v in enumerate(key):
                            value += '"{}",'.format(key_param[i])

                        values = ", ".join('{0}=values({0})'.format(k) for k in key)

                        sql = '''insert into diablo_quality_tags ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                            ",".join(key), value[:-1], values)
                        # print(sql)
                        cursor.execute(sql)
                        mysql_conn.commit()

                    except Exception as e:
                        print("写入品质质保信息失败 - mysql：", e)
                    finally:
                        mysql_conn.close()

                    return {}

    def init_hongde_data(self, merchant_id):
        # 初始化品质字典
        hongde_quality_data, err = self.init_hongde_quality_data(merchant_id)
        if err:
            return

        self.init_quality = hongde_quality_data

        # 初始化质保字典
        hongde_zhibao_data, err = self.init_hongde_zhibao_data(merchant_id)
        if err:
            return

        self.init_zhibao = hongde_zhibao_data

    # 任务执行
    def task(self):
        q = Queue(maxsize=40)  # 设定最大队列数和线程数

        count = 1

        for merchant_id in self.main_group:
            self.init_hongde_data(merchant_id)
            if merchant_id == "3":
                # 获取所有库存记录
                stock_related_list = self.get_stock_related("1390f84351fe464b90dcdbf3a164ff24")
            else:
                stock_related_list = self.get_stock_related("3")

            # print(merchant_id, f"stock_related_list{stock_related_list}")
            for stock_related in stock_related_list:

                goods_cst_id = stock_related.get("goods_cst_id")

                # 获取库存商品列表
                goods_list = self.get_cst_goods(goods_cst_id)

                for goods in goods_list:

                    t = threading.Thread(target=self.write_data, args=(goods, stock_related))
                    q.put(t)
                    if q.full() == True:
                        thread_list = []
                        while q.empty() == False:
                            t = q.get()
                            thread_list.append(t)
                            t.start()
                        for t in thread_list:
                            t.join()

                    print("当前写入数：", count,
                          ", 商家为：", stock_related.get("merchant_name"),
                          ", 品质为：", goods.get("brand_names"))

                    count += 1

                # 处理残余队列数据
                thread_list = []
                while q.empty() == False:
                    t = q.get()
                    thread_list.append(t)
                    t.start()
                for t in thread_list:
                    t.join()


if __name__ == "__main__":
    QualityInit()
